package site.sorghum.ddl.dialect;

import site.sorghum.ddl.entity.DdlColumnWrap;
import site.sorghum.ddl.entity.DdlIndexWrap;
import site.sorghum.ddl.entity.DdlTableWrap;
import site.sorghum.ddl.enums.DbType;
import site.sorghum.ddl.field.OracleTypeConvert;
import site.sorghum.ddl.field.TypeConverter;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;

public class OracleDDLDialect extends AbstractDDLDialect {

    @Override
    public DbType getDatabaseType() {
        return DbType.ORACLE;
    }

    @Override
    public TypeConverter typeConverter() {
        return OracleTypeConvert.INSTANCE;
    }

    @Override
    protected String quoteIdentifier(String identifier) {
        return "\"" + identifier.toUpperCase() + "\"";
    }

    @Override
    protected String quoteValue(Object value) {
        if (value instanceof String) {
            return "'" + ((String) value).replace("'", "''") + "'";
        }
        return String.valueOf(value);
    }

    @Override
    protected String getColumnCommentClause(DdlColumnWrap column) {
        // Oracle 列注释需要单独的 COMMENT 语句
        return "";
    }

    @Override
    public List<String> generateCreateTableDDL(DdlTableWrap table) {
        List<String> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder(super.generateCreateTableDDL(table).get(0));

        // 添加表注释
        if (table.getRemarks() != null && !table.getRemarks().isEmpty()) {
            sql.append("\nCOMMENT ON TABLE ").append(quoteIdentifier(table.getName())).append(" IS ").append(quoteValue(table.getRemarks()));
        }

        // 添加列注释
        for (DdlColumnWrap column : table.getColumns()) {
            if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
                sql.append(";\nCOMMENT ON COLUMN ").append(quoteIdentifier(table.getName())).append(".").append(quoteIdentifier(column.getName())).append(" IS ").append(quoteValue(column.getRemarks()));
            }
        }
        // 如果最后没有;结尾
        if (!Objects.equals(sql.charAt(sql.length() - 1), ';')) {
            sql.append(";");
        }
        result.add(sql.toString());
        for (DdlIndexWrap index : table.getIndexes()) {
            String generateIndexDDL = generateAddIndexDDL(index);
            result.add(generateIndexDDL);
        }
        return result;
    }

    @Override
    public String generateAddIndexDDL(String indexName, List<DdlColumnWrap> columnWraps, boolean unique) {
        if (columnWraps.isEmpty()) {
            return "";
        }

        String tableName = columnWraps.get(0).getTable();
        if (indexName == null|| indexName.isEmpty()){
            indexName = DdlIndexWrap.columnUniqueIndexName(columnWraps.stream().map(DdlColumnWrap::getName).collect(Collectors.toList()), unique);
        }
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE ");
        if (unique) {
            sql.append("UNIQUE ");
        }
        sql.append("INDEX ").append(quoteIdentifier(indexName)).append(" ON ")
                .append(quoteIdentifier(tableName)).append(" (")
                .append(columnWraps.stream()
                        .map(DdlColumnWrap::getName)
                        .map(this::quoteIdentifier)
                        .collect(Collectors.joining(", ")))
                .append(")");
        sql.append(";");
        return sql.toString();
    }

    @Override
    public String generateDropColumnDDL(String table, String column) {
        return "ALTER TABLE " + quoteIdentifier(table) +
                " DROP COLUMN " + quoteIdentifier(column) + ";";
    }

    @Override
    public String generateDropIndexDDL(String table, String indexName) {
        return "DROP INDEX " + quoteIdentifier(indexName) + ";";
    }

    @Override
    public DdlTableWrap fromDataSource(String schema,
                                       String tableName,
                                       Function<String, List<String>> pks,
                                       Function<String, DdlTableWrap> table,
                                       Function<String, List<DdlColumnWrap>> columns,
                                       Function<String, List<DdlIndexWrap>> indexes) {
        // 生成获取当前表信息的语句SQL
        String tableSql = String.format("SELECT TABLE_NAME    AS name,\n" +
                "       COMMENTS        AS remarks\n" +
                "FROM ALL_TABLES\n" +
                "WHERE TABLE_NAME = '%s';", tableName.toUpperCase());
        // 生成获取列信息的SQL
        String columnSql = String.format("SELECT '%s'                   as tableName,\n" +
                "       COLUMN_NAME              AS name,\n" +
                "       DATA_TYPE                AS dbType,\n" +
                "       DATA_LENGTH              AS size,\n" +
                "       DATA_PRECISION           AS digit,\n" +
                "       NULLABLE                 AS isNullable,\n" +
                "       DATA_DEFAULT             AS defaultValue,\n" +
                "       COMMENTS                 AS remarks\n" +
                "FROM ALL_TAB_COLUMNS\n" +
                "WHERE TABLE_NAME = '%s';", tableName.toUpperCase(), tableName.toUpperCase());
        // 生成主键获取信息的SQL
        String pksSql = String.format("SELECT ACC.COLUMN_NAME AS name\n" +
                "FROM ALL_CONSTRAINTS AC\n" +
                "JOIN ALL_CONS_COLUMNS ACC ON AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME\n" +
                "WHERE AC.TABLE_NAME = '%s'\n" +
                "  AND AC.CONSTRAINT_TYPE = 'P';", tableName.toUpperCase());
        // 索引的SQL
        String indexSql = String.format("SELECT \n" +
                "    '%s' AS tableName,\n" +
                "    i.INDEX_NAME AS indexName,\n" +
                "    LISTAGG(c.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY c.COLUMN_POSITION) AS columnNames,\n" +
                "    CASE WHEN i.UNIQUENESS = 'UNIQUE' THEN 1 ELSE 0 END AS isUnique\n" +
                "FROM \n" +
                "    ALL_INDEXES i\n" +
                "JOIN \n" +
                "    ALL_IND_COLUMNS c ON i.INDEX_NAME = c.INDEX_NAME AND i.TABLE_NAME = c.TABLE_NAME\n" +
                "WHERE \n" +
                "    i.TABLE_NAME = '%s'\n" +
                "    AND i.OWNER = '%s'\n" +
                "    AND i.INDEX_NAME NOT LIKE 'SYS_%%'\n" +
                "    AND i.INDEX_TYPE = 'NORMAL'\n" +
                "GROUP BY \n" +
                "    i.INDEX_NAME, i.UNIQUENESS", tableName.toUpperCase(),tableName.toUpperCase(),tableName.toUpperCase());
        DdlTableWrap tableWrap = table.apply(tableSql);
        List<DdlColumnWrap> columnWraps = columns.apply(columnSql);
        List<String> pkList = pks.apply(pksSql);
        List<DdlIndexWrap> indexWraps = indexes.apply(indexSql);
        tableWrap.setColumns(columnWraps);
        tableWrap.setPks(pkList);
        tableWrap.setIndexes(indexWraps);
        return tableWrap;
    }

    @Override
    public List<String> fetchAllTableNames(String schema, Function<String, List<String>> tables) {
        // 生成获取所有表名的SQL
        String sql = String.format("SELECT TABLE_NAME\n" +
                "FROM ALL_TABLES\n" +
                "WHERE OWNER = '%s';", schema.toUpperCase());
        // 使用传入的函数获取表名列表
        return tables.apply(sql);
    }

    @Override
    protected String getNullableClause(DdlColumnWrap column) {
        return Boolean.TRUE.equals(column.getIsNullable()) ? " NULL" : " NOT NULL";
    }
}